﻿using Dapper;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace GraduationProject.RDBMS
{
    public class Comment : OpearteBase
    {
        /// <summary>
        /// 增加评论
        /// </summary>
        /// <param name="ExcuteResult"></param>
        /// <param name="Comments"></param>
        /// <returns></returns>
        public bool TryInsertComment(out bool ExcuteResult, params Model.Comment[] Comments)
        {
            ExcuteResult = false;
            try
            {
                if (Comments != null && Comments.Count() > 0)
                {

                    //打开数据库事务
                    TryOpenTrans();
                    //更新数据库
                    StringBuilder Str_SQL = new StringBuilder();
                    #region 封装SQL语句
                    Str_SQL.AppendLine("INSERT INTO [dbo].[Comment]");
                    Str_SQL.AppendLine("([NovelID]");
                    Str_SQL.AppendLine(",[CommentBody]");
                    Str_SQL.AppendLine(",[GoalCommentID]");
                    Str_SQL.AppendLine(",[AgreeNum]");
                    Str_SQL.AppendLine(",[DisagreeNum]");
                    Str_SQL.AppendLine(",[VoteUser]");
                    Str_SQL.AppendLine(",[CreateTime]");
                    Str_SQL.AppendLine(",[CreateUser])");
                    Str_SQL.AppendLine("VALUES");
                    Str_SQL.AppendLine("(@NovelID");
                    Str_SQL.AppendLine(",@CommentBody");
                    Str_SQL.AppendLine(",@GoalCommentID");
                    Str_SQL.AppendLine(",@AgreeNum");
                    Str_SQL.AppendLine(",@DisagreeNum");
                    Str_SQL.AppendLine(",@VoteUser");
                    Str_SQL.AppendLine(",@CreateTime");
                    Str_SQL.AppendLine(",@CreateUser)");
                    #endregion

                    if (m_Conn.Execute(Str_SQL.ToString(), Comments, m_Transaction) > 0)
                    {
                        ExcuteResult = true;
                        IsDone &= true;
                    }
                    else
                    {
                        ExcuteResult = false;
                        IsDone &= false;
                    }
                }
            }

            catch (Exception ex)
            {
                IsDone &= false;
                Console.WriteLine(ex);
            }
            return IsDone;
        }
        /// <summary>
        /// 删除评论
        /// </summary>
        /// <param name="ExcuteResult"></param>
        /// <param name="CommentIDs"></param>
        /// <returns></returns>
        public bool TryDeleteComment(out bool ExcuteResult, params int[] CommentIDs)
        {
            ExcuteResult = false;
            try
            {
                if (CommentIDs != null && CommentIDs.Length > 0)
                {
                    StringBuilder Str_SQL = new StringBuilder();
                    #region 封装SQL语句
                    Str_SQL.AppendLine("DELETE FROM [dbo].[Comment]");
                    Str_SQL.AppendLine("WHERE [dbo].[Comment].[CommentID] IN @IDs");
                    #endregion

                    TryOpenTrans();
                    if (m_Conn.Execute(Str_SQL.ToString(), new { IDs = CommentIDs }, m_Transaction) > 0)
                    {
                        ExcuteResult = true;
                        IsDone &= true;
                    }
                    else
                    {
                        ExcuteResult = false;
                        IsDone &= false;
                    }
                }
            }
            catch (Exception ex)
            {
                IsDone &= false;
                Console.WriteLine(ex);
            }
            return IsDone;
        }
        /// <summary>
        /// 更新评论信息
        /// </summary>
        /// <param name="ExcuteResult"></param>
        /// <param name="Comments"></param>
        /// <returns></returns>
        public bool TryUpdateComment(out bool ExcuteResult, params Model.Comment[] Comments)
        {
            ExcuteResult = false;
            try
            {
                if (Comments != null && Comments.Length > 0)
                {
                    StringBuilder Str_SQL = new StringBuilder();
                    #region 封装SQL语句
                    Str_SQL.AppendLine("UPDATE [dbo].[Comment]");
                    Str_SQL.AppendLine("SET [NovelID] = @NovelID");
                    Str_SQL.AppendLine(",[CommentBody] = @CommentBody");
                    Str_SQL.AppendLine(",[GoalCommentID] = @GoalCommentID");
                    Str_SQL.AppendLine(",[AgreeNum] = @AgreeNum");
                    Str_SQL.AppendLine(",[DisagreeNum] = @DisagreeNum");
                    Str_SQL.AppendLine(",[VoteUser] = @VoteUser");
                    Str_SQL.AppendLine(",[CreateTime] = @CreateTime");
                    Str_SQL.AppendLine(",[CreateUser] = @CreateUser");
                    Str_SQL.AppendLine(" WHERE [CommentID] = @CommentID");
                    #endregion

                    TryOpenTrans();
                    if (m_Conn.Execute(Str_SQL.ToString(), Comments, m_Transaction) > 0)
                    {
                        ExcuteResult = true;
                        IsDone &= true;
                    }
                    else
                    {
                        ExcuteResult = false;
                        IsDone &= false;
                    }
                }
            }
            catch (Exception ex)
            {
                IsDone &= false;
                Console.WriteLine(ex);
            }
            return IsDone;
        }
        /// <summary>
        /// 查询评论信息
        /// </summary>
        /// <param name="Comments"></param>
        /// <param name="CommentIDs"></param>
        /// <returns></returns>
        public bool TrySelectComment(out Model.Comment[] Comments, params int[] CommentIDs)
        {
            //预先定义返回值
            Comments = null;
            try
            {
                StringBuilder Str_SQL_Where = new StringBuilder();
                #region 封装Where条件语句
                if (CommentIDs != null && CommentIDs.Length > 0)
                {
                    if (Str_SQL_Where.Length == 0)
                    {
                        Str_SQL_Where.AppendLine("WHERE");
                    }
                    if (Str_SQL_Where.Length > 7)
                    {
                        Str_SQL_Where.AppendLine("AND");
                    }
                    if (Str_SQL_Where.Length >= 7)
                    {
                        Str_SQL_Where.AppendLine("[dbo].[Comment].[CommentID] IN @IDs");
                    }
                }
                #endregion

                StringBuilder Str_Sql = new StringBuilder();
                #region 封装SQL语句
                Str_Sql.AppendLine("SELECT");
                Str_Sql.AppendLine("dbo.Comment.CommentID,");
                Str_Sql.AppendLine("dbo.Comment.NovelID,");
                Str_Sql.AppendLine("dbo.Comment.CommentBody,");
                Str_Sql.AppendLine("dbo.Comment.GoalCommentID,");
                Str_Sql.AppendLine("dbo.Comment.AgreeNum,");
                Str_Sql.AppendLine("dbo.Comment.DisagreeNum,");
                Str_Sql.AppendLine("dbo.Comment.VoteUser,");
                Str_Sql.AppendLine("dbo.Comment.CreateTime,");
                Str_Sql.AppendLine("dbo.Comment.CreateUser,");
                Str_Sql.AppendLine("dbo.[User].UserName AS CreateUserName,");
                Str_Sql.AppendLine("dbo.[User].HeadPortrait AS CreateUserHeadPortrait");
                Str_Sql.AppendLine("FROM");
                Str_Sql.AppendLine("dbo.Comment");
                Str_Sql.AppendLine("INNER JOIN dbo.[User] ON dbo.Comment.CreateUser = dbo.[User].UserID");

                //判断是否存在查询条件
                if (Str_SQL_Where.Length > 0)
                {
                    Str_Sql.Append(Str_SQL_Where);
                }
                #endregion

                var SQL_Data = m_Conn.Query<Model.Comment>(Str_Sql.ToString(), new { IDs = CommentIDs }, m_Transaction);
                if (SQL_Data != null && SQL_Data.Count() > 0)
                {
                    Comments = SQL_Data.ToArray();
                    return true;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }
            return false;
        }
        /// <summary>
        /// 根据文章查询评论信息
        /// </summary>
        /// <param name="Comments"></param>
        /// <param name="NovelID"></param>
        /// <returns></returns>
        public bool TrySelectCommentByNovelID(out Model.Comment[] Comments, int NovelID)
        {
            //预先定义返回值
            Comments = null;
            try
            {
                StringBuilder Str_SQL_Where = new StringBuilder();
                #region 封装Where条件语句
                if (NovelID != 0)
                {
                    if (Str_SQL_Where.Length == 0)
                    {
                        Str_SQL_Where.AppendLine("WHERE");
                    }
                    if (Str_SQL_Where.Length > 7)
                    {
                        Str_SQL_Where.AppendLine("AND");
                    }
                    if (Str_SQL_Where.Length >= 7)
                    {
                        Str_SQL_Where.AppendLine("[dbo].[Comment].[NovelID] = @NovelID");
                    }
                }
                #endregion

                StringBuilder Str_Sql = new StringBuilder();
                #region 封装SQL语句
                Str_Sql.AppendLine("SELECT");
                Str_Sql.AppendLine("dbo.Comment.CommentID,");
                Str_Sql.AppendLine("dbo.Comment.NovelID,");
                Str_Sql.AppendLine("dbo.Comment.CommentBody,");
                Str_Sql.AppendLine("dbo.Comment.GoalCommentID,");
                Str_Sql.AppendLine("dbo.Comment.AgreeNum,");
                Str_Sql.AppendLine("dbo.Comment.DisagreeNum,");
                Str_Sql.AppendLine("dbo.Comment.VoteUser,");
                Str_Sql.AppendLine("dbo.Comment.CreateTime,");
                Str_Sql.AppendLine("dbo.Comment.CreateUser,");
                Str_Sql.AppendLine("dbo.[User].UserName AS CreateUserName,");
                Str_Sql.AppendLine("dbo.[User].HeadPortrait AS CreateUserHeadPortrait");
                Str_Sql.AppendLine("FROM");
                Str_Sql.AppendLine("dbo.Comment");
                Str_Sql.AppendLine("INNER JOIN dbo.[User] ON dbo.Comment.CreateUser = dbo.[User].UserID");
                //判断是否存在查询条件
                if (Str_SQL_Where.Length > 0)
                {
                    Str_Sql.Append(Str_SQL_Where);
                }
                #endregion

                var SQL_Data = m_Conn.Query<Model.Comment>(Str_Sql.ToString(), new { NovelID }, m_Transaction);


                var Out_Put = new List<Model.Comment>();
                if (SQL_Data != null && SQL_Data.Count() > 0)
                {
                    foreach (var Data in SQL_Data)
                    {
                        bool IsAdd = false;
                        foreach (var item in Out_Put)
                        {
                            if (item.TrySetComments(Data))
                            {
                                IsAdd = true;
                                break;
                            }
                        }
                        if (!IsAdd)
                        {
                            Out_Put.Add(Data);
                        }
                    }
                }
                if (Out_Put != null && Out_Put.Count > 0)
                {
                    Comments = Out_Put.ToArray();
                    return true;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }
            return false;
        }
        /// <summary>
        /// 评论点赞
        /// </summary>
        /// <param name="CommentID"></param>
        /// <param name="UserID"></param>
        /// <returns></returns>
        public bool TryAddAgreeNum(out bool ExcuteResult, int CommentID, int UserID)
        {
            ExcuteResult = false;
            try
            {
                if (CommentID != 0 && UserID != 0)
                {
                    var Str_SQL_Exist = new StringBuilder();
                    #region 封装查重语句
                    Str_SQL_Exist.AppendLine("SELECT");
                    Str_SQL_Exist.AppendLine("Count(dbo.CommentAgreeList.ID)");
                    Str_SQL_Exist.AppendLine("FROM");
                    Str_SQL_Exist.AppendLine("dbo.CommentAgreeList");
                    Str_SQL_Exist.AppendLine("WHERE");
                    Str_SQL_Exist.AppendLine("dbo.CommentAgreeList.UserID = @UserID AND");
                    Str_SQL_Exist.AppendLine("dbo.CommentAgreeList.CommentID = @CommentID");
                    #endregion
                    if (m_Conn.ExecuteScalar<int>(Str_SQL_Exist.ToString(), new { CommentID, UserID }, m_Transaction) > 0)
                    {
                        //存在重复值，不允许点赞
                        ExcuteResult = false;
                        IsDone &= true;
                    }
                    else
                    {
                        //不存在重复值，允许点赞
                        var Str_SQL_Num = new StringBuilder();
                        #region 封装数量更改SQL语句
                        Str_SQL_Num.AppendLine("UPDATE [dbo].[Comment]");
                        Str_SQL_Num.AppendLine("SET [AgreeNum] = [AgreeNum] + 1");
                        Str_SQL_Num.AppendLine("WHERE");
                        Str_SQL_Num.AppendLine("dbo.Comment.CommentID = @CommentID");
                        #endregion

                        //不存在重复值，允许点赞
                        var Str_SQL_Insert = new StringBuilder();
                        #region 封装记录插入SQL语句
                        Str_SQL_Insert.AppendLine("INSERT INTO [dbo].[CommentAgreeList]");
                        Str_SQL_Insert.AppendLine("([UserID]");
                        Str_SQL_Insert.AppendLine(",[CommentID])");
                        Str_SQL_Insert.AppendLine("VALUES");
                        Str_SQL_Insert.AppendLine("(@UserID");
                        Str_SQL_Insert.AppendLine(",@CommentID)");
                        #endregion

                        TryOpenTrans();

                        //执行数量更改语句
                        if (m_Conn.Execute(Str_SQL_Num.ToString(), new { CommentID, UserID }, m_Transaction) > 0)
                        {
                            ExcuteResult = true;
                            IsDone &= true;
                        }
                        else
                        {
                            ExcuteResult = false;
                            IsDone &= false;
                        }

                        //执行记录插入语句
                        if (m_Conn.Execute(Str_SQL_Insert.ToString(), new { CommentID, UserID }, m_Transaction) > 0)
                        {
                            ExcuteResult = true;
                            IsDone &= true;
                        }
                        else
                        {
                            ExcuteResult = false;
                            IsDone &= false;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                IsDone &= false;
                Console.WriteLine(ex);
            }
            return IsDone;
        }
        /// <summary>
        /// 评论修改点赞量（管理员功能,默认置零）
        /// </summary>
        /// <returns></returns>
        public bool TryUpdateAgreeNum(out bool ExcuteResult, int CommentID, int AgreeNum = 0)
        {
            ExcuteResult = false;
            try
            {
                if (CommentID != 0)
                {
                    var Str_SQL_Update = new StringBuilder();
                    #region 封装数量修改SQL语句
                    Str_SQL_Update.AppendLine("UPDATE [dbo].[Comment]");
                    Str_SQL_Update.AppendLine("SET [AgreeNum] = @AgreeNum");
                    Str_SQL_Update.AppendLine("WHERE [CommentID] = @CommentID");
                    #endregion

                    var Str_SQL_Delete = new StringBuilder();
                    #region 封装记录删除语句
                    Str_SQL_Delete.AppendLine("Delete [dbo].[CommentAgreeList]");
                    Str_SQL_Delete.AppendLine("WHERE [CommentID] = @CommentID");
                    #endregion

                    TryOpenTrans();
                    //进行修改操作
                    if (m_Conn.Execute(Str_SQL_Update.ToString(), new { CommentID, AgreeNum }, m_Transaction) > 0)
                    {
                        ExcuteResult = true;
                        IsDone &= true;
                    }
                    else
                    {
                        ExcuteResult = false;
                        IsDone &= false;
                    }

                    //进行记录删除操作
                    if (m_Conn.Execute(Str_SQL_Delete.ToString(), new { CommentID }, m_Transaction) > 0)
                    {
                        ExcuteResult = true;
                        IsDone &= true;
                    }
                    else
                    {
                        ExcuteResult = false;
                        IsDone &= false;
                    }
                }
            }
            catch (Exception ex)
            {
                IsDone &= false;
                Console.WriteLine(ex);
            }
            return IsDone;
        }
        /// <summary>
        /// 评论点反对
        /// </summary>
        /// <param name="CommentID"></param>
        /// <param name="UserID"></param>
        /// <returns></returns>
        public bool TrySubDisAgreeNum(out bool ExcuteResult, int CommentID, int UserID)
        {
            ExcuteResult = false;
            try
            {
                if (CommentID != 0 && UserID != 0)
                {
                    var Str_SQL_Exist = new StringBuilder();
                    #region 封装查重语句
                    Str_SQL_Exist.AppendLine("SELECT");
                    Str_SQL_Exist.AppendLine("Count(dbo.CommentDisAgreeList.ID)");
                    Str_SQL_Exist.AppendLine("FROM");
                    Str_SQL_Exist.AppendLine("dbo.CommentDisAgreeList");
                    Str_SQL_Exist.AppendLine("WHERE");
                    Str_SQL_Exist.AppendLine("dbo.CommentDisAgreeList.UserID = @UserID AND");
                    Str_SQL_Exist.AppendLine("dbo.CommentDisAgreeList.CommentID = @CommentID");
                    #endregion
                    if (m_Conn.ExecuteScalar<int>(Str_SQL_Exist.ToString(), new { CommentID, UserID }, m_Transaction) > 0)
                    {
                        //存在重复值，不允许点反对
                        ExcuteResult = false;
                        IsDone &= true;
                    }
                    else
                    {
                        //不存在重复值，允许点反对
                        var Str_SQL_Num = new StringBuilder();
                        #region 封装数量更改SQL语句
                        Str_SQL_Num.AppendLine("UPDATE [dbo].[Comment]");
                        Str_SQL_Num.AppendLine("SET [DisAgreeNum] = [DisAgreeNum] + 1");
                        Str_SQL_Num.AppendLine("WHERE");
                        Str_SQL_Num.AppendLine("dbo.Comment.CommentID = @CommentID");
                        #endregion

                        //不存在重复值，允许点反对
                        var Str_SQL_Insert = new StringBuilder();
                        #region 封装记录插入SQL语句
                        Str_SQL_Insert.AppendLine("INSERT INTO [dbo].[CommentDisAgreeList]");
                        Str_SQL_Insert.AppendLine("([UserID]");
                        Str_SQL_Insert.AppendLine(",[CommentID])");
                        Str_SQL_Insert.AppendLine("VALUES");
                        Str_SQL_Insert.AppendLine("(@UserID");
                        Str_SQL_Insert.AppendLine(",@CommentID)");
                        #endregion

                        TryOpenTrans();

                        //执行数量更改语句
                        if (m_Conn.Execute(Str_SQL_Num.ToString(), new { CommentID, UserID }, m_Transaction) > 0)
                        {
                            ExcuteResult = true;
                            IsDone &= true;
                        }
                        else
                        {
                            ExcuteResult = false;
                            IsDone &= false;
                        }

                        //执行记录插入语句
                        if (m_Conn.Execute(Str_SQL_Insert.ToString(), new { CommentID, UserID }, m_Transaction) > 0)
                        {
                            ExcuteResult = true;
                            IsDone &= true;
                        }
                        else
                        {
                            ExcuteResult = false;
                            IsDone &= false;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                IsDone &= false;
                Console.WriteLine(ex);
            }
            return IsDone;
        }
        /// <summary>
        /// 评论修改反对量（管理员功能,默认置零）
        /// </summary>
        /// <returns></returns>
        public bool TryUpdateDisAgreeNum(out bool ExcuteResult, int CommentID, int DisAgreeNum = 0)
        {
            ExcuteResult = false;
            try
            {
                if (CommentID != 0)
                {
                    var Str_SQL_Update = new StringBuilder();
                    #region 封装数量修改SQL语句
                    Str_SQL_Update.AppendLine("UPDATE [dbo].[Comment]");
                    Str_SQL_Update.AppendLine("SET [DisAgreeNum] = @DisAgreeNum");
                    Str_SQL_Update.AppendLine("WHERE [CommentID] = @CommentID");
                    #endregion

                    var Str_SQL_Delete = new StringBuilder();
                    #region 封装记录删除语句
                    Str_SQL_Delete.AppendLine("Delete [dbo].[CommentDisAgreeList]");
                    Str_SQL_Delete.AppendLine("WHERE [CommentID] = @CommentID");
                    #endregion

                    TryOpenTrans();
                    //进行修改操作
                    if (m_Conn.Execute(Str_SQL_Update.ToString(), new { CommentID, DisAgreeNum }, m_Transaction) > 0)
                    {
                        ExcuteResult = true;
                        IsDone &= true;
                    }
                    else
                    {
                        ExcuteResult = false;
                        IsDone &= false;
                    }

                    //进行记录删除操作
                    if (m_Conn.Execute(Str_SQL_Delete.ToString(), new { CommentID }, m_Transaction) > 0)
                    {
                        ExcuteResult = true;
                        IsDone &= true;
                    }
                    else
                    {
                        ExcuteResult = false;
                        IsDone &= false;
                    }
                }
            }
            catch (Exception ex)
            {
                IsDone &= false;
                Console.WriteLine(ex);
            }
            return IsDone;
        }
    }
}
